//get grant schedule over income and ability from npsas tables I downloaded.

//merit grants_avg_10k_upto2000_cleaned//merit adjustments
import delimited "$data/npsas/merit_by_gpa_cleaned.csv", clear
gen cat = 1
replace cat = 2 if v3 == "p4y"
replace cat = 3 if v3 == "bpr"
replace cat = 4 if v3 == "gpr"
drop v3
drop if _n>20

gen gpa = word(v1, -1)
drop if gpa == "100"
gen ability = 0
replace ability = 1 if gpa == "200"
replace ability = 2 if gpa == "300"
replace ability = 3 if gpa == "301"
drop gpa

replace v2 = "0" if cat == 1 | cat == 3
destring v2, replace
ren v2 grant_merit
drop v1
replace grant_merit = grant_merit/.78235 //deflate
replace grant_merit = grant_merit/10000 //normalize, x4

reshape wide grant_merit, i(cat) j(ability)
save "$temp/npsas_merit", replace


//get grants
import delimited "$data/npsas/grants_avg_10k_upto2000_cleaned.csv", clear
gen cat = 1
replace cat = 2 if v4 == "p4y"
replace cat = 3 if v4 == "bpr"
replace cat = 4 if v4 == "gpr"
drop v4

//parent income bounds
gen pinc_upper = word(v1, -1)
drop if pinc_upper == "Total"
destring pinc_upper, replace
drop v1
drop if _n>56

//federal aid
ren v3 aid_fed
destring aid_fed, replace force
replace aid_fed = 0 if aid_fed == .

//state aid
replace v2 = "450" in 39
replace v2 = "450" in 40
replace v2 = "450" in 41
replace v2 = "450" in 42
replace v2 = "85" in 8
replace v2 = "85" in 9
replace v2 = "85" in 10
replace v2 = "85" in 11
replace v2 = "85" in 12
replace v2 = "85" in 13
replace v2 = "85" in 14
replace v2 = subinstr(v2, "!", "", .)
replace v2 = trim(v2)
destring v2, replace
destring pinc_upper, replace
ren v2 aid_state

//deflate and normalize
ds cat, not
foreach var in `r(varlist)'{
	replace `var' = `var'/ .78235
	replace `var' = `var' / 10000
}
replace pinc = pinc/4


merge m:1 cat using "$temp/npsas_merit", keep(match) nogen
replace aid_state = aid_state - grant_merit0
gen aid_total_1 = aid_state + aid_fed + grant_merit1
gen aid_total_2 = aid_state + aid_fed + grant_merit2
gen aid_total_3 = aid_state + aid_fed + grant_merit3

drop grant*  aid_state
order cat pinc_upper aid_total* aid_fed
replace pinc = 100 if pinc>6.3910015


//public 4-years
keep if cat == 2
drop cat

//renormalize
ds
foreach var in `r(varlist)'{
    replace `var' = `var' * 40000
	replace `var' = `var' / 47961.46
}


ds aid*
foreach var in `r(varlist)'{
    replace `var' = `var'/18
}

export delimited "$model/utilities/student_aid.csv", replace novarn




///78.235